<?php
global $route;
global $x;



//config 
define("MAX_SHIFT_LEN", 8);
define("MIN_SHIFT_LEN", 3);
define("MAX_HOURS_PER_DAY", 8);
define("MAX_HOURS_WEEK", 35);
define("MAX_DAYS_PER_WEEK", 5);
define("MIN_SHIFT_BUFFER", 10);




//db settings
//
$_CONF['dbtype']        = "MYSQL"; //this is not used yet as only db being used is MySQL
$_CONF['dbuser']        = "username";
$_CONF['dbpass']        = "password";
$_CONF['dbhost']        = "localhost";
$_CONF['dbname']        = "dbname";

$path = "/home/pi";

require("lom.inc");


//Connect to Database 
if ($_CONF['dbtype'] == "MYSQL"){
        $connect = mysql_connect($_CONF['dbhost'], $_CONF['dbuser'], $_CONF['dbpass']);
        if(!$connect){die('Could not connect: ' . mysql_error());}

        $dbase=mysql_select_db($_CONF['dbname'], $connect);
        if(!$dbase){die('didnt get db: ' . mysql_error());}
}//if ($_CONF['dbtype'] == "MYSQL")


//Database Functions
function lominsert($sql){
	$query=mysql_query($sql);
	if (mysql_error()){ return mysql_error();}
	else{$return =  mysql_insert_id(); return $return;}
}

function lomgetit($sql){
	$result=mysql_query($sql);
	if (mysql_error()){return mysql_error();}

	if (count($result) == 0){return 0;}
      else{
      	for ($count=0; $count < mysql_num_rows($result); $count ++)
            {$row[]=mysql_fetch_assoc($result);}
            mysql_free_result($result);
            return $row;
        }//end else
}//end function lomgetit($sql)


function verify_scheduling_protocols($start, $stop, $employee){

        //verify employee has enough hours
        $hours = get_employee_sked_hours($employee);
        $shift_len = ($stop - $start)/3600;
        $total_hours = $hours  + $shift_len;

        //verify employee has days left that week
        $days =  get_employee_day_count($employee);

        if (($hours + $shift_len > MAX_HOURS_WEEK)
        or ($days == MAX_DAYS_PER_WEEK)){return 0;}
        else{return 1;}
        return $return;
}


function sked_shift($start, $stop, $employee, $avail = null, $need = null){
        //insert into sked table
        $sql  = "insert into sked ";
        $sql .= "set start = {$start}, ";
        $sql .= "stop = {$stop}, ";
        if (isset($need)){$sql .= "need = {$need}, ";}
        $sql .= "employee = {$employee}, ";
        $sql .= "ts = unix_timestamp() ";
	lom::insert($sql);
	if (isset($avail)){
                //mark as processed in avail
                $sql  = "update avail ";
                $sql .= "set processed = 1 ";
                $sql .= "where auto = {$avail}";
        	print "<h1>$sql</h1>"; print lom::insert($sql);
	}
	if (isset($need)){
                //mark as processed in need
                $sql  = "update need ";
                $sql .= "set processed = 1 ";
                $sql .= "where auto = {$need}";
        	print "<h1>$sql</h1>"; print lom::insert($sql);
	}
	if ($error == 1){return 0;}
	else{return 1;}
}


function get_days_to_sked($year, $week){
	$sql  = "select distinct day(from_unixtime(`start`)) as day, ";
	$sql .= "month(from_unixtime(`start`)) as month, ";
	$sql .= "year(from_unixtime(`start`)) as year ";
	$sql .= "from need where processed = 0 ";
	$sql .= "and week(from_unixtime(`start`), 3) = {$week} ";
	$sql .= "order by start asc";
	$days = lom::getit($sql);

	return $days;
}

function reset_db(){
	$sql = "update need set processed = 0";
	lom::insert($sql);
	$sql = "update avail set processed = 0";

	lom::insert($sql);

	$sql = "delete from sked";
	lom::insert($sql);
}

function simple_table($data){
	$return = "<table border=1;>\n";

    	$return .= "\t<tr>";
    	// Take the keys from the first row as the headings
    	foreach (array_keys($data[0]) as $heading) {$return .= '<th>' . $heading . '</th>';}
	$return .= "</tr>\n";

	foreach ($data as $row) {
		$return .= "<tr>";
		foreach ($row as $column){$return .= "<td>$column</td>";}
   		$return .= "</tr>\n";
	}
	$return .= "</table>\n\n";
	print $return;
}



function find_avail_shift($start, $stop){

	$shift_len = ($stop - $start)/3600;

        $sql = "select *, (stop - start)/3600 as hours from avail ";
        $sql .= "where ";
        $sql .= "start >= $start and stop <= $stop and ";
        $sql .= "min <= $shift_len and ";
        $sql .= "max >= $shift_len and ";
        $sql .= "processed = 0 ";
        $sql .= "order by max asc, min asc";

        $results = lom::getit($sql);
        return $results;

}




function get_employee_day_count($employee){

	$sql  = "select count(distinct day(from_unixtime(`start`))) as days from sked ";
        $sql .= "where employee = $employee";
        $results = lom::getit($sql);

        if ($results[0]['days'] == MAX_DAYS_PER_WEEK){
                $sql = "update avail set processed = 1 where employee = $employee";
                lom::insert($sql);
        }
	if ($results[0]['days'] == 0){$return = 0;}else{$return = $results[0]['days'];}
	//print "[methods.php] Employee $employee has {$return} hours\n\n\n";

        return $return;

}


function get_employee_sked_hours($employee){
//print "sked_hours ==> $employee<br><br>";


$sql  = "select sum((stop - start)/3600) as `Scheduled Hours` ";
$sql .= "from sked";

$results = lom::getit($sql);
$sked_hours = $results[0]['Scheduled Hours'];

$sql  = "select sum((stop - start)/3600) as `Needed Hours` ";
$sql .= "from need";

$results = lom::getit($sql);
$need_hours = $results[0]['Needed Hours'];

if ($sked_hours > $need_hours){print "<h1>Houston, weve got a problem: $need_hours</h1>\n";
}//if



        $sql  = "select sum((stop - start)/3600) as hours from sked ";
        $sql .= "where employee = $employee ";
	//$sql .= "and week(from_unixtime(`start`), 3) = {$_REQUEST['week']} ";
        $results = lom::getit($sql);
        if ($results[0]['hours'] > MAX_HOURS_WEEK){
                $sql = "update avail set processed = 1 where employee = $employee";
                lom::insert($sql);
        }

        return $results[0]['hours'];

}

function get_employee_sked_same_day($employee, $start){


	$day =  date("Y-m-d", $start);
	$next = date("Y-m-d", strtotime("+1day", $start));

	$sql  = "select count(auto) as count ";
	$sql .= "from sked ";
	$sql .= "where stop ";
	$sql .= "between unix_timestamp('$day') and unix_timestamp('$next')" ;
	$sql .= "and employee = $employee ";
//	$sql .= "where from_unixtime(`start`) = {$day} ";
//	$sql .= "or from_unixtime(`stop`) = {$day} ";
print "$sql\n";
	$results = lom::getit($sql);

        return $results[0]['count'];

}


function get_slots_day($day, $month, $year){

	//Select needed shifts by day
	$sql  = "select * , day(from_unixtime(`start`)) as day, ";
	$sql .= "month(from_unixtime(`start`)) as month, ";
	$sql .= "year(from_unixtime(`start`)) as year,  ";
        $sql .= "(stop - start)/3600 as hours, ";
        $sql .= "from_unixtime(`start`) as `Start Time`, ";
        $sql .= "from_unixtime(`stop`) as `Stop Time` ";
	$sql .= "from need ";
	$sql .= "where processed = 0 ";
	$sql .= "and day(from_unixtime(`start`)) = $day ";
        $sql .= "and month(from_unixtime(`start`)) = $month ";
        $sql .= "and year(from_unixtime(`start`)) = $year ";
	$sql .= "order by (stop - start)/3600";
//day(from_unixtime(`start`)) asc, start asc";

	$results = lom::getit($sql);
	return $results;
}



function get_avail_day($day, $month, $year){

        //Select needed shifts by day
        $sql  = "select * , day(from_unixtime(`start`)) as day, ";
        $sql .= "month(from_unixtime(`start`)) as month, ";
        $sql .= "year(from_unixtime(`start`)) as year,  ";
        $sql .= "(stop - start)/3600 as hours, ";
        $sql .= "from_unixtime(`start`) as `Start Time`, ";
        $sql .= "from_unixtime(`stop`) as `Stop Time` ";
        $sql .= "from avail ";
        $sql .= "where processed = 0 ";
        $sql .= "and day(from_unixtime(`start`)) = $day ";
        $sql .= "and month(from_unixtime(`start`)) = $month ";
        $sql .= "and year(from_unixtime(`start`)) = $year ";
        $sql .= "order by start asc, stop asc";

//print $sql;

        //$sql .= "order by (stop - start)/3600";
//day(from_unixtime(`start`)) asc, start asc";

        $results = lom::getit($sql);
        return $results;
}




function get_need_avail_stats(){
	$sql = "select sum(need) as sum from need_avail";
	$results = lom::getit($sql);
	$return['needed_hours'] = $results[0]['sum'];
	$return['employee_count'] = get_employee_count();
	$return['hours_per_employee'] = get_hours_per_employee();
	$return['average_shift_length'] = average_shift_length();
	return $return;

}


function get_need_avail(){
	$sql = "select * from need_avail order by start asc";
        $results = lom::getit($sql);
        return $results;


}


//get all needs
function get_need(){
	$sql = "select *, (stop - start)/3600 as hours from need ";
	$sql .= "where processed = 0 order by (stop - start)/3600 asc";
	$results = lom::getit($sql);
        return $results;

}


function get_avail($start, $stop){

	//TODO: change select to be sure start + min <= stop
	//and start + max >= stop

        $sql  = "select *, (stop - start)/3600 as hours, ";
	$sql .= "(SELECT sked.ts FROM `sked` WHERE sked.employee = avail.employee ";
	$sql .= "order by sked.ts desc limit 0,1) as last_action "; 
        $sql .= " from avail where start <= $start and stop >= $stop  and ";
	$sql .= "min <= ($stop - $start)/3600 and  max >= ($stop - $start)/3600 and ";
        $sql .= "processed = 0 order by ";
	$sql .= "(SELECT sum((sked.stop - sked.start)/3600) FROM `sked` WHERE sked.employee = avail.employee ";
        $sql .= "order by sked.ts desc limit 0,1) asc, ";
	$sql .= "hours asc, min asc, max asc";
/*
        $sql  = "select *, (stop - start)/3600 as hours ";
        $sql .= " from avail where start <= $start and stop >= $stop  and ";
	$sql .= "min <= ($stop - $start)/3600 and  max >= ($stop - $start)/3600 and ";
        $sql .= "processed = 0 order by ";
	$sql .= "hours asc, min asc, max asc";
*/

print "$sql\n";
        $results = lom::getit($sql);
print_r($results);

        return $results;
}

function get_avail_start_stop($start, $stop){

        //TODO: change select to be sure start + min <= stop
        //and start + max >= stop

        $sql = "select *, (stop - start)/3600 as hours from avail ";
        $sql .= "where ";
	$sql .= "((start <= $start and stop >= $start + (min * 3600)) or  ";
	$sql .= "(start <= $stop and stop >= $stop + (min * 3600)) or";
	$sql .= "(start >= $start and stop <= $stop + (min * 3600))) ";
        $sql .= "and processed = 0 order by start asc, stop asc";


//        $sql .= "(SELECT sum((sked.stop - sked.start)/3600) FROM `sked` WHERE sked.employee = avail.employee ";
//        $sql .= "order by sked.ts desc limit 0,1) asc, ";
//	$sql .= "hours desc, max desc, min desc";
print "$sql\n";
        $results = lom::getit($sql);
	return $results;
}

function get_avail_stop($stop){

	//TODO: change select to be sure start + min <= stop
	//and start + max >= stop

        $sql = "select *, (stop - start)/3600 as hours from avail ";
        $sql .= "where ";
	$sql .= "start <= $stop - (min * 3600) and ";
        $sql .= "stop >= $stop and ";
        $sql .= "processed = 0 order by ";
        $sql .= "(SELECT sum((sked.stop - sked.start)/3600) FROM `sked` WHERE sked.employee = avail.employee ";
        $sql .= "order by sked.ts desc limit 0,1) asc, ";
        $sql .= "hours desc, max desc, min desc";
print "$sql<br>\n";
        $results = lom::getit($sql);
lom::loop($results);
        return $results;
}

function get_avail_start($start){

        //TODO: change select to be sure start + min <= stop
        //and start + max >= stop

        $sql = "select *, (stop - start)/3600 as hours from avail ";
        $sql .= "where ";
        $sql .= "start <= $start and stop >= $start + (min * 3600) and ";
        $sql .= "processed = 0 order by ";
	$sql .= "(SELECT sum((sked.stop - sked.start)/3600) FROM `sked` WHERE sked.employee = avail.employee ";
        $sql .= "order by sked.ts desc limit 0,1) asc, ";
        $sql .= "hours desc, max desc, min desc";
        $results = lom::getit($sql);
        return $results;
}


function get_avail_min_max($start, $stop){
        $sql = "select * from avail where ";
        $sql .= "(start <= {$start} and ";  //avail starts before and stops before stop
        $sql .= "stop >= {$start}) or ";
        $sql .= "(start >= {$start} and ";  //avail after start and ends before stop
        $sql .= "stop <= {$stop}) or ";
        $sql .= "(start <= {$stop} and ";  //avail after start and ends after stop
	$sql .= "stop >= {$stop}) ";
        $sql .= "order by max desc, min desc";

	$results = lom::getit($sql);
	return $results;
}



function get_employee_count(){
        $sql = "select count(distinct employee) as employees from avail";
        $results = lom::getit($sql);
        return $results[0]['employees'];

}

function get_hours_per_employee(){
        $hours = get_needed_hours();
        $employees = get_employee_count();
        return $hours/$employees;
}

function average_shift_length(){
        $hours = get_hours_per_employee();
        $employees = get_employee_count();

        if ($hours/AVERAGE_DAYS_PER_WEEK > MAX_SHIFT_LEN){return $MAX_SHIFT_LEN;}
        else{return floor($hours/AVERAGE_DAYS_PER_WEEK);}
}

function get_needed_hours(){
        $sql = "select sum((stop - start)/3600) as hours from need ";
        $sql .= "where processed = 0";
        $results = lom::getit($sql);
        return $results[0]['hours'];
}



/*  hours
Monday - Thursday  7:00am-10:00pm
Friday                     7:00am-9:00pm
Saturday                 8:00am-8:00pm
Sunday                  10:00am-8:00pm

*/
